"""
Case Type   : ustore_DQL_hint
Case Name   : 从本地向date_dim表中导入数据
Create At   : 2022-04-10
Owner       : lonely-dance
Description :
    1、使用copy命令插入数据
    2、环境清理
Expect      :
    1、插入数据成功
    2、清理成功
History     :
"""

import os
import subprocess
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class HintAdd(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----- {os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_root = Node('PrimaryRoot')
        self.pri_node = Node('PrimaryDbUser')
        self.com = Common()
        self.constant = Constant()
        self.t_name1 = 't_col_date_dim'
        self.t_name2 = 't_date_dim'
        self.copy_file = 'date_dim_hint.txt'
        self.cur_path = os.path.join(macro.SCRIPTS_PATH, self.copy_file)
        self.parent_path = os.path.dirname(macro.DB_INSTANCE_PATH)
        self.target_path = os.path.join(self.parent_path, self.copy_file)

    def test_join(self):
        text = '-----step1:创建表 expect: 创建表成功-----'
        self.log.info(text)
        cmd_1 = f"""
            drop table if exists {self.t_name1};
            create table {self.t_name1}
            (
                d_date_sk                 integer               not null,
                d_date_id                 char(16)              not null,
                d_date                    date                          ,
                d_month_seq               integer                       ,
                d_week_seq                integer                       ,
                d_quarter_seq             integer                       ,
                d_year                    integer                       ,
                d_dow                     integer                       ,
                d_moy                     integer                       ,
                d_dom                     integer                       ,
                d_qoy                     integer                       ,
                d_fy_year                 integer                       ,
                d_fy_quarter_seq          integer                       ,
                d_fy_week_seq             integer                       ,
                d_day_name                char(9)                       ,
                d_quarter_name            char(6)                       ,
                d_holiday                 char(1)                       ,
                d_weekend                 char(1)                       ,
                d_following_holiday       char(1)                       ,
                d_first_dom               integer                       ,
                d_last_dom                integer                       ,
                d_same_day_ly             integer                       ,
                d_same_day_lq             integer                       ,
                d_current_day             char(1)                       ,
                d_current_week            char(1)                       ,
                d_current_month           char(1)                       ,
                d_current_quarter         char(1)                       ,
                d_current_year            char(1)
            )
            WITH (STORAGE_TYPE=USTORE) partition by Range(d_year) (
                    partition p1 values less than(1950),
                    partition p2 values less than(2000),
                    partition p3 values less than(2050),
                    partition p4 values less than(2100),
                    partition p5 values less than(3000),
                    partition p6 values less than(maxvalue)
            );
            """
        self.log.info(cmd_1)

        cmd_2 = f"""
            drop table if exists {self.t_name2};
            create table  {self.t_name2}
            (
                d_date_sk                 integer               not null,
                d_date_id                 char(16)              not null,
                d_date                    date                          ,
                d_month_seq               integer                       ,
                d_week_seq                integer                       ,
                d_quarter_seq             integer                       ,
                d_year                    integer                       ,
                d_dow                     integer                       ,
                d_moy                     integer                       ,
                d_dom                     integer                       ,
                d_qoy                     integer                       ,
                d_fy_year                 integer                       ,
                d_fy_quarter_seq          integer                       ,
                d_fy_week_seq             integer                       ,
                d_day_name                char(9)                       ,
                d_quarter_name            char(6)                       ,
                d_holiday                 char(1)                       ,
                d_weekend                 char(1)                       ,
                d_following_holiday       char(1)                       ,
                d_first_dom               integer                       ,
                d_last_dom                integer                       ,
                d_same_day_ly             integer                       ,
                d_same_day_lq             integer                       ,
                d_current_day             char(1)                       ,
                d_current_week            char(1)                       ,
                d_current_month           char(1)                       ,
                d_current_quarter         char(1)                       ,
                d_current_year            char(1)
             )
            WITH (STORAGE_TYPE=USTORE) partition by Range(d_year) (
                    partition p1 values less than(1950),
                    partition p2 values less than(2000),
                    partition p3 values less than(2050),
                    partition p4 values less than(2100),
                    partition p5 values less than(3000),
                    partition p6 values less than(maxvalue)
            );
            """
        self.log.info(cmd_2)
        res_1 = self.pri_sh.execut_db_sql(cmd_1)
        self.log.info(res_1)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, res_1, "表创建失败")
        res_2 = self.pri_sh.execut_db_sql(cmd_2)
        self.log.info(res_2)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, res_2, "表创建失败")

        step_txt = '-----step2:使用copy命令插入数据，expect: 数据插入成功-----'
        self.log.info(step_txt)
        text = '-----step2.1: 获取当前主机的IP地址;expect: 成功-----'
        self.log.info(text)
        cmd = "hostname -I"
        self.log.info(cmd)
        self.cur_ip = subprocess.getoutput(cmd).strip()
        self.log.info(self.cur_ip)

        text = '-----step2.2: 复制文件到数据库主机对应目录 expect: 成功-----'
        self.log.info(text)
        if self.pri_sh.node.ssh_host in self.cur_ip:
            self.log.info('当前主机是数据库主机，无需远程复制')
            cmd = f"cp {self.cur_path} {self.parent_path};"
            self.log.info(cmd)
            res = self.pri_sh.node.sh(cmd).result()
            self.log.info(res)
        else:
            self.com.scp_file(self.pri_node, self.copy_file,
                              self.parent_path)

        text = '-----step2.3: 查看数据库主机目标路径下文件 expect: 成功-----'
        self.log.info(text)
        cmd = f"ls -l {self.parent_path}| grep -E '{self.copy_file}'"
        self.log.info(cmd)
        res = self.pri_root.sh(cmd).result()
        self.log.info(res)
        self.assertNotIn('No such file or directory', res, '文件拷贝失败')
        self.assertIn(f'{self.copy_file}', res, '文件拷贝失败')

        text = '-----step2.4:使用copy命令插入数据，expect: 数据插入成功-----'
        self.log.info(text)
        self.log.info('---------------使用copy命令插入数据------------------')
        copy_sql = f"copy {self.t_name1} from '{self.target_path}'; "
                   
        self.log.info(copy_sql)
        copy_result = self.pri_sh.execut_db_sql(copy_sql)
        self.log.info(copy_result)
        self.assertEqual(copy_result.strip().splitlines()[0].strip(),
                         'COPY 50', '执行失败:' + step_txt)

        text = '-----step2.5:使用copy命令插入数据，expect: 数据插入成功-----'
        self.log.info(text)
        self.log.info('---------------使用copy命令插入数据------------------')
        copy_sql = f"copy {self.t_name2} from '{self.target_path}'; "
                   
        self.log.info(copy_sql)
        copy_result = self.pri_sh.execut_db_sql(copy_sql)
        self.log.info(copy_result)
        self.assertEqual(copy_result.strip().splitlines()[0].strip(),
                         'COPY 50', '执行失败:' + step_txt)

    def tearDown(self):
        self.log.info('-----this is teardown-----')
        step_txt = '-----step3: 环境清理，expect:清理成功-----'
        self.log.info(step_txt)
        self.log.info('由于后续用例依赖表，故先不删除')
        rm_cmd = f"rm -rf {self.target_path} "
        self.log.info(rm_cmd)
        rm_res = self.pri_root.sh(rm_cmd).result()
        self.log.info(rm_res)
        self.assertEqual(rm_res, '', '文件删除失败' + step_txt)
        self.log.info(f'----- {os.path.basename(__file__)} end-----')
